Imports System.Collections.Specialized
Imports System.Configuration.Provider
Imports System.Data
Imports System.Data.SqlClient
Imports System.Security.Permissions
Imports System.Web.Configuration

Public Class SqlQuestionnaireProvider
    Inherits QuestionnaireProvider

    ' Fields
    Private _applicationName As String
    Private _connectionString As String
    Private _connectionStringName As String
    Private _CommandTimeout As Integer


#Region "Initialized Sub and Properties"

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)

        'Verify that config isn't null
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        'Assign the provider a default name if it doesn't have one
        If String.IsNullOrEmpty(name) Then
            name = "SqlQuestionnaireProvider"
        End If

        'Add a default "description" attribute to config if the
        'Attribute(doesn) 't exist or is empty
        If String.IsNullOrEmpty(config.Item("description")) Then
            config.Remove("description")
            config.Add("description", "SQL Questionnaire Provider")
        End If

        'Call the base class's Initialize method
        MyBase.Initialize(name, config)

        'Initialize _applicationName
        _applicationName = config("applicationName")

        If (String.IsNullOrEmpty(_applicationName)) Then _applicationName = "/"

        config.Remove("applicationName")

        'Initialize _connectionString
        _connectionStringName = config("connectionStringName")

        If (String.IsNullOrEmpty(_connectionStringName)) Then _
            Throw New ProviderException("Empty or missing connectionStringName")

        If WebConfigurationManager.ConnectionStrings(_connectionStringName) Is Nothing Then _
            Throw New ProviderException("Missing connection string")

        _connectionString = WebConfigurationManager.ConnectionStrings(_connectionStringName).ConnectionString()

        If (String.IsNullOrEmpty(_connectionString)) Then _
            Throw New ProviderException("Empty connection string")

        config.Remove("connectionStringName")

        'Retrive a commandTimeout
        Dim _Timeout As String = config("commandTimeout")
        If String.IsNullOrEmpty(_Timeout) Then
            _Timeout = "30"
        End If
        If Not Integer.TryParse(_Timeout, _CommandTimeout) OrElse _CommandTimeout < 0 Then
            Throw New ProviderException("Value must be non negative integer")
        End If

        config.Remove("commandTimeout")

        'Throw an exception if unrecognized attributes remain
        If (config.Count > 0) Then
            Dim attr As String = config.GetKey(0)
            If (Not String.IsNullOrEmpty(attr)) Then
                Throw New ProviderException("Unrecognized attribute: " + attr)
            End If
        End If

    End Sub

    Public Overrides Property ApplicationName() As String
        Get
            Return Me._applicationName
        End Get
        Set(ByVal value As String)
            If String.IsNullOrEmpty(value) Then
                _applicationName = "/"
            End If
            If (value.Length > 256) Then
                Throw New ProviderException("Provider application name is too long")
            End If
            Me._applicationName = value
        End Set
    End Property

    Public Property ConnectionStringName() As String
        Get
            Return _connectionStringName
        End Get
        Set(ByVal value As String)
            If (String.IsNullOrEmpty(value)) Then _
                Throw New ProviderException("Empty or missing connectionStringName")
            _connectionStringName = value
            If WebConfigurationManager.ConnectionStrings(value) Is Nothing Then _
                Throw New ProviderException("Missing connection string for " & value)
            _connectionString = WebConfigurationManager.ConnectionStrings(value).ConnectionString()
            If (String.IsNullOrEmpty(_connectionString)) Then _
                Throw New ProviderException("Empty connection string for " & value)
        End Set
    End Property

#End Region


#Region "For Category Manager"

    Public Overrides Function CreateCategory(ByVal name As String, ByVal desc As String) As Category
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_CategoryCreate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Name", SqlDbType.NVarChar, name))
                cmd.Parameters.Add(Me.CreateInputParam("@Desc", SqlDbType.NVarChar, desc))
                Dim pId = New SqlParameter("@Id", SqlDbType.SmallInt)
                pId.Direction = ParameterDirection.Output
                cmd.Parameters.Add(pId)
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                conn.Open()
                cmd.ExecuteNonQuery()
                Dim Id As Integer = IIf((pId.Value Is Nothing), -1, CType(pId.Value, Integer))
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Return Nothing
                Else
                    Return New Category(Id, name, desc, 0)
                End If
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error creating Questionnaire Category")
        End Try
    End Function

    Public Overrides Function DeleteCategory(ByVal id As Integer, ByVal name As String) As Boolean
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_CategoryDelete", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@ID", SqlDbType.Int, id))
                cmd.Parameters.Add(Me.CreateInputParam("@Name", SqlDbType.NVarChar, name))
                Dim p1 As New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                Return result > 0
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw New ProviderException("Error deleting Questionnaire Category")
        End Try
    End Function

    Public Overrides Function GetCategories() As System.Data.DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_CategoriesGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Categories")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw ex
            Throw New ProviderException("Error getting Questionnaire Categories")
        End Try
        Return ds.Tables(0).DefaultView
    End Function

    Public Overrides Function GetCategory(ByVal id As Integer) As String
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_CategoryGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.SmallInt, id))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Return Me.GetNullableString(reader, 0)
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw New ProviderException("Error getting Questionnaire Category")
        End Try
    End Function

    Public Overrides Sub UpdateCategory(ByVal cat As Category)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_CategoryUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@ID", SqlDbType.Int, cat.ID))
                cmd.Parameters.Add(Me.CreateInputParam("@Name", SqlDbType.NVarChar, cat.Name))
                cmd.Parameters.Add(Me.CreateInputParam("@Desc", SqlDbType.NVarChar, cat.Description))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw New ProviderException("Update Category NOT success")
            'Throw ex
        End Try
    End Sub

#End Region


#Region "Private Subs and Functions"

    Private Function CreateInputParam( _
        ByVal paramName As String, _
        ByVal dbType As SqlDbType, _
        ByVal objValue As Object) As SqlParameter

        Dim p1 As New SqlParameter(paramName, dbType)
        If (objValue Is Nothing) Then
            p1.IsNullable = True
            p1.Value = DBNull.Value
            Return p1
        End If
        p1.Value = objValue
        Return p1
    End Function

    Private Function GetNullableString( _
        ByVal reader As SqlDataReader, _
        ByVal col As Integer) As String
        If reader.IsDBNull(col) Then
            Return String.Empty
        Else
            Return reader.GetString(col)
        End If
    End Function

    Private Function RoundToSeconds(ByVal dt As DateTime) As DateTime
        Return New DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second)
    End Function

#End Region


#Region "For Question"

    Public Overrides Function CreateQuestion( _
    ByVal categoryId As Integer, _
    ByVal question As String, _
    ByVal configuration As String, _
    Optional ByVal choices As DataView = Nothing) As Question

        Dim objTransaction As SqlTransaction = Nothing
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                objTransaction = conn.BeginTransaction()

                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionCreate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Transaction = objTransaction
                cmd.Parameters.Add(Me.CreateInputParam("@CategoryId", SqlDbType.SmallInt, categoryId))
                cmd.Parameters.Add(Me.CreateInputParam("@Question", SqlDbType.NVarChar, question))
                cmd.Parameters.Add(Me.CreateInputParam("@Configuration", SqlDbType.NVarChar, configuration))
                Dim pId = New SqlParameter("@Id", SqlDbType.TinyInt)
                pId.Direction = ParameterDirection.Output
                cmd.Parameters.Add(pId)
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim Id As Integer = IIf((pId.Value Is Nothing), -1, CType(pId.Value, Integer))
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Creating Question Error; the result <> 0.")
                Else
                    If Not choices Is Nothing Then
                        ' Create Choices
                        Dim sid As Integer = 0
                        For Each dr As DataRowView In choices
                            Dim cmdChoice As New SqlCommand("dotnetpm_Questionnaire_QuestionChoiceCreate", conn)
                            cmdChoice.CommandTimeout = _CommandTimeout
                            cmdChoice.CommandType = CommandType.StoredProcedure
                            cmdChoice.Transaction = objTransaction
                            cmdChoice.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, Id))
                            cmdChoice.Parameters.Add(Me.CreateInputParam("@SortId", SqlDbType.TinyInt, sid))
                            cmdChoice.Parameters.Add(Me.CreateInputParam("@Choice", SqlDbType.NVarChar, dr("Choice")))
                            cmdChoice.ExecuteNonQuery()
                            sid += 1
                        Next
                    End If
                    objTransaction.Commit()
                    Return New Question(Id, categoryId, question, configuration)
                End If
            Finally
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                    conn = Nothing
                    objTransaction = Nothing
                End If
            End Try
        Catch ex As Exception
            If Not objTransaction Is Nothing Then
                objTransaction.Rollback()
            End If
            Throw ex
            'Throw New ProviderException("Error creating Questionnaire Question")
        End Try
    End Function

    Public Overrides Function DeleteQuestion(ByVal id As Integer) As Boolean

        Dim objTransaction As SqlTransaction = Nothing
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                objTransaction = conn.BeginTransaction()

                ' Deleting the Existing Choices
                Dim delChoice As New SqlCommand("dotnetpm_Questionnaire_QuestionChoicesDelete", conn)
                delChoice.CommandTimeout = _CommandTimeout
                delChoice.CommandType = CommandType.StoredProcedure
                delChoice.Transaction = objTransaction
                delChoice.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, id))
                delChoice.ExecuteNonQuery()

                ' Deleting the question
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionDelete", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Transaction = objTransaction
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.Int, id))
                Dim p1 As New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result > 0 Then
                    objTransaction.Commit()
                    Return True
                Else
                    Throw New ProviderException("Deleting Question Error; result NOT > 0.")
                End If
            Finally
                conn.Close()
                conn = Nothing
                objTransaction = Nothing
            End Try
        Catch ex As Exception
            If Not objTransaction Is Nothing Then
                objTransaction.Rollback()
            End If
            Throw ex
            'Throw New ProviderException("Deleting Question Error")
        End Try
    End Function

    Public Overrides Function GetChoices(ByVal id As Integer) As System.Data.DataTable
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_QuestionChoicesGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, id))
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Choices")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Questionnaire Choices")
        End Try
        Return ds.Tables(0)
    End Function

    Public Overrides Function GetQuestion(ByVal id As Integer) As Question
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.Int, id))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Dim categoryId As Integer = reader.GetInt16(1)
                        Dim question As String = Me.GetNullableString(reader, 2)
                        Dim configuration As String = Me.GetNullableString(reader, 3)
                        Return New Question(id, categoryId, question, configuration)
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Error getting Questionnaire Question")
            Throw ex
        End Try
    End Function

    Public Overloads Overrides Function GetQuestions( _
    ByVal categoryId As Integer, _
    ByVal stringToSearch As String) As System.Data.DataView

        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_QuestionsGet", conn)
                cmd.Parameters.Add(CreateInputParam("@CategoryId", SqlDbType.SmallInt, categoryId))
                cmd.Parameters.Add(CreateInputParam("@StringToSearch", SqlDbType.NVarChar, stringToSearch))
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Questions")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw ex
            Throw New ProviderException("Error getting Questionnaire Questions")
        End Try
        Return ds.Tables(0).DefaultView
    End Function

    Public Overrides Sub UpdateQuestion( _
    ByVal question As Question, _
    Optional ByVal choices As DataView = Nothing)

        Dim objTransaction As SqlTransaction = Nothing
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try

                conn.Open()
                objTransaction = conn.BeginTransaction()

                If Not choices Is Nothing Then
                    ' Delete Existing Choices
                    Dim delChoice As New SqlCommand("dotnetpm_Questionnaire_QuestionChoicesDelete", conn)
                    delChoice.CommandTimeout = _CommandTimeout
                    delChoice.CommandType = CommandType.StoredProcedure
                    delChoice.Transaction = objTransaction
                    delChoice.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, question.ID))
                    delChoice.ExecuteNonQuery()

                    ' Create New Choices
                    Dim sid As Integer = 0
                    For Each dr As DataRowView In choices
                        Dim cmdChoice As New SqlCommand("dotnetpm_Questionnaire_QuestionChoiceCreate", conn)
                        cmdChoice.CommandTimeout = _CommandTimeout
                        cmdChoice.CommandType = CommandType.StoredProcedure
                        cmdChoice.Transaction = objTransaction
                        cmdChoice.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, question.ID))
                        cmdChoice.Parameters.Add(Me.CreateInputParam("@SortId", SqlDbType.TinyInt, sid))
                        cmdChoice.Parameters.Add(Me.CreateInputParam("@Choice", SqlDbType.NVarChar, dr("Choice")))
                        cmdChoice.ExecuteNonQuery()
                        sid += 1
                    Next
                End If

                ' Update the Question
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Transaction = objTransaction
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.Int, question.ID))
                cmd.Parameters.Add(Me.CreateInputParam("@CategoryId", SqlDbType.SmallInt, question.CategoryID))
                cmd.Parameters.Add(Me.CreateInputParam("@Question", SqlDbType.NVarChar, question.Question))
                cmd.Parameters.Add(Me.CreateInputParam("@Configuration", SqlDbType.NVarChar, question.Configuration))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Updating Question Error; result <> 0.")
                End If

                objTransaction.Commit()
            Finally
                conn.Close()
                conn = Nothing
                objTransaction = Nothing
            End Try
        Catch ex As Exception
            If Not objTransaction Is Nothing Then
                objTransaction.Rollback()
            End If
            Throw New ProviderException("Updating Question Error")
            'Throw ex
        End Try
    End Sub

#End Region


#Region "For Instrument"

    Public Overrides Function CreateInstrument( _
    ByVal instrumentId As String, _
    ByVal name As String) As Instrument
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentCreate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@Name", SqlDbType.NVarChar, name))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcCreatedDate", SqlDbType.NVarChar, curTime))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                conn.Open()
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("A duplicated InstrumentID is founded on ID = " & result & ".")
                    Return Nothing
                Else
                    Return New Instrument(name, instrumentId)
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error creating Instrument")
        End Try
    End Function

    Public Overrides Function DeleteInstrument(ByVal instrumentId As String) As Boolean
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentDelete", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@instrumentId", SqlDbType.VarChar, instrumentId))
                Dim p1 As New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                Return result > 0
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw New ProviderException("Error deleting instrument.")
        End Try
    End Function

    Public Overrides Function GetInstrument(ByVal instrumentId As String) As Instrument
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.VarChar, instrumentId))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Dim name As String = Me.GetNullableString(reader, 1)
                        Dim _instrument As New Instrument(instrumentId, name)
                        With _instrument
                            .Description = Me.GetNullableString(reader, 2)
                            .Instruction = Me.GetNullableString(reader, 3)
                            If Not reader.IsDBNull(4) Then
                                .CreatedDate = reader.GetDateTime(4).ToLocalTime
                            End If
                            If Not reader.IsDBNull(5) Then
                                .LastUpdated = reader.GetDateTime(5).ToLocalTime
                            End If
                            .Locked = reader.GetBoolean(6)
                        End With
                        Return _instrument
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instrument")
        End Try
    End Function

    Public Overrides Function GetInstruments( _
    ByVal fieldToSearch As String, _
    ByVal stringToSearch As String) As DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_InstrumentsGet", conn)
                cmd.Parameters.Add(CreateInputParam("@FieldToSearch", SqlDbType.NVarChar, fieldToSearch))
                cmd.Parameters.Add(CreateInputParam("@StringToSearch", SqlDbType.NVarChar, stringToSearch))
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Instruments")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instruments")
        End Try
        Return ds.Tables(0).DefaultView
    End Function

    Public Overrides Sub UpdateInstrument( _
    ByVal instrument As Instrument, _
    ByVal original_instrumentId As String)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                'Throw New Exception("id = " & questionnaire.ID & ", name = " & questionnaire.Description)
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrument.InstrumentID))
                cmd.Parameters.Add(Me.CreateInputParam("@Original_InstrumentId", SqlDbType.NVarChar, original_instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@Name", SqlDbType.NVarChar, instrument.Name))
                cmd.Parameters.Add(Me.CreateInputParam("@Description", SqlDbType.NVarChar, instrument.Description))
                cmd.Parameters.Add(Me.CreateInputParam("@Instruction", SqlDbType.NVarChar, instrument.Instruction))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcLastUpdate", SqlDbType.NVarChar, curTime))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Result <> 0.")
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update the instrument.")
            Throw ex
        End Try
    End Sub

    Public Overrides Sub LockInstrument(ByVal instrumentId As String)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentAnswerTableCreate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcTime", SqlDbType.NVarChar, curTime))
                conn.Open()
                cmd.ExecuteNonQuery()
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Failed to add a logic into the instrument.")
        End Try
    End Sub

#End Region


#Region "For Instrument Questions"

    Public Overrides Function AddInstrumentQuestion( _
    ByVal instrumentId As String, _
    ByVal questionId As Integer) As Boolean
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentQuestionAdd", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.VarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, questionId))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                conn.Open()
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Return False
                Else
                    Return True
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error adding question to the instrument.")
        End Try
    End Function

    Public Overrides Function DeleteInstrumentQuestion(ByVal uid As String) As Boolean
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentQuestionDelete", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@UID", SqlDbType.UniqueIdentifier, New Guid(uid)))
                Dim p1 As New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                Return result > 0
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw New ProviderException("Error deleting Delete Question from the instrument.")
        End Try
    End Function

    Public Overrides Function GetInstrumentQuestions(ByVal instrumentId As String) As System.Data.DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_InstrumentQuestionsGet", conn)
                cmd.Parameters.Add(CreateInputParam("@InstrumentId", SqlDbType.VarChar, instrumentId))
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_InstrumentQuestions")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Questions for Questionnaire; ID = " & instrumentId)
        End Try
        Return ds.Tables(0).DefaultView
    End Function

    Public Overrides Sub UpdateInstrumentQuestion(ByVal uid As String, ByVal questionID As Integer)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentQuestionUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@UID", SqlDbType.UniqueIdentifier, New Guid(uid)))
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionID", SqlDbType.Int, questionID))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update instrument question.")
            Throw ex
        End Try
    End Sub

#End Region


#Region "For Instrument Logics"

    Public Overrides Function AddInstrumentLogic( _
    ByVal parentUid As String, _
    ByVal questionId As Integer, _
    ByVal logic As String) As Boolean
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentLogicAdd", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@ParentUid", SqlDbType.UniqueIdentifier, New Guid(parentUid)))
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, questionId))
                cmd.Parameters.Add(Me.CreateInputParam("@Logic", SqlDbType.NVarChar, logic))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                conn.Open()
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Return False
                Else
                    Return True
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Failed to add a logic into the instrument.")
        End Try
    End Function

    Public Overrides Function GetInstrumentLogic(ByVal uid As String) As Logic
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentLogicGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Uid", SqlDbType.UniqueIdentifier, New Guid(uid)))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Dim logic As New Logic
                        logic.UID = uid
                        logic.ParentUID = reader.GetGuid(1).ToString
                        logic.Logic = Me.GetNullableString(reader, 2)
                        If Not reader.IsDBNull(3) Then
                            logic.QuestionID = reader.GetInt32(3)
                        End If
                        logic.SortID = reader.GetByte(4)
                        logic.Locked = reader.GetBoolean(5)
                        Return logic
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Logic")
        End Try
    End Function

    Public Overrides Function GetInstrumentLogics(ByVal parentUid As String) As System.Data.DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_InstrumentLogicsGet", conn)
                cmd.Parameters.Add(CreateInputParam("@ParentUid", SqlDbType.UniqueIdentifier, New Guid(parentUid)))
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_InstrumentLogics")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Logics.")
        End Try
        Return ds.Tables(0).DefaultView
    End Function

    Public Overrides Sub UpdateInstrumentLogic( _
    ByVal uid As String, _
    ByVal questionId As Integer, _
    ByVal logic As String)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_InstrumentLogicUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                'Throw New Exception("Uid = " & UID & ", Logic = " & Logic)
                cmd.Parameters.Add(Me.CreateInputParam("@Uid", SqlDbType.UniqueIdentifier, New Guid(uid)))
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionId", SqlDbType.Int, questionId))
                cmd.Parameters.Add(Me.CreateInputParam("@Logic", SqlDbType.NVarChar, logic))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update a logic for the instrument.")
            Throw ex
        End Try
    End Sub

#End Region


#Region "For Questionnaire"

    Public Overrides Function CreateQuestionnaire( _
    ByVal studyId As String, _
    ByVal visitNumber As Byte, _
    ByVal instrumentId As String, _
    ByVal desc As String, _
    ByVal createdBy As String) As Questionnaire
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireCreate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                cmd.Parameters.Add(Me.CreateInputParam("@StudyId", SqlDbType.NVarChar, studyId))
                cmd.Parameters.Add(Me.CreateInputParam("@VisitNumber", SqlDbType.TinyInt, visitNumber))
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.Parameters.Add(Me.CreateInputParam("@Description", SqlDbType.NVarChar, desc))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcCreatedDate", SqlDbType.NVarChar, curTime))
                cmd.Parameters.Add(Me.CreateInputParam("@CreatedBy", SqlDbType.NVarChar, createdBy))
                Dim pId = New SqlParameter("@Id", SqlDbType.TinyInt)
                pId.Direction = ParameterDirection.Output
                cmd.Parameters.Add(pId)
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.BigInt)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                conn.Open()
                cmd.ExecuteNonQuery()
                Dim Id As Integer = IIf((pId.Value Is Nothing), -1, CType(pId.Value, Integer))
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 OrElse Id < 0 Then
                    Throw New ProviderException("Creating Question Error; the result <> 0.")
                Else
                    Dim quest As New Questionnaire(Id)
                    With quest
                        .StudyID = studyId
                        .VisitNumber = visitNumber
                        .InstrumentID = instrumentId
                        .Description = desc
                        .CreatedDate = curTime.ToLocalTime
                        .CreatedBy = createdBy
                        .LastUpdated = .CreatedDate
                        .LastUpdatedBy = createdBy
                    End With
                    Return quest
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error creating Instrument")
        End Try
    End Function


    Public Overrides Function DeleteQuestionnaire( _
    ByVal questionnaireId As Integer) As Boolean
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireDelete", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.VarChar, questionnaireId))
                Dim p1 As New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                Return result > 0
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw New ProviderException("Error deleting questionnaire.")
        End Try
    End Function


    Public Overrides Function GetQuestionnaire( _
    ByVal questionnaireId As Integer) As Questionnaire
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireGet", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.BigInt, questionnaireId))
                Dim reader As SqlDataReader = Nothing
                Try
                    reader = cmd.ExecuteReader
                    If reader.Read Then
                        Dim _questionnaire As New Questionnaire(questionnaireId)
                        With _questionnaire
                            .StudyID = Me.GetNullableString(reader, 1)
                            .InstrumentID = Me.GetNullableString(reader, 2)
                            .VisitNumber = reader.GetByte(3)
                            .Description = Me.GetNullableString(reader, 4)
                            .EdtingPermission = Me.GetNullableString(reader, 5)
                            .TakingPermission = Me.GetNullableString(reader, 6)
                            If Not reader.IsDBNull(7) Then
                                .CreatedDate = reader.GetDateTime(7).ToLocalTime
                            End If
                            .CreatedBy = Me.GetNullableString(reader, 8)
                            If Not reader.IsDBNull(9) Then
                                .LastUpdated = reader.GetDateTime(9).ToLocalTime
                            End If
                            .LastUpdatedBy = Me.GetNullableString(reader, 10)
                        End With
                        Return _questionnaire
                    End If
                    Return Nothing
                Finally
                    If (Not reader Is Nothing) Then
                        reader.Close()
                    End If
                End Try
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instrument")
        End Try
    End Function


    Public Overloads Overrides Function GetQuestionnaires( _
    ByVal studyId As String, _
    ByVal instrumentId As String) As DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_QuestionnairesGet", conn)
                cmd.Parameters.Add(CreateInputParam("@StudyId", SqlDbType.NVarChar, studyId))
                cmd.Parameters.Add(CreateInputParam("@InstrumentId", SqlDbType.NVarChar, instrumentId))
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Questionnaires")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instruments")
        End Try
        Return ds.Tables(0).DefaultView
    End Function


    Public Overrides Sub UpdateQuestionnaire( _
    ByVal questionnaire As Questionnaire)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                'Throw New Exception("id = " & questionnaire.ID & ", name = " & questionnaire.Description)
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.BigInt, questionnaire.ID))
                cmd.Parameters.Add(Me.CreateInputParam("@StudyId", SqlDbType.NVarChar, questionnaire.StudyID))
                cmd.Parameters.Add(Me.CreateInputParam("@VisitNumber", SqlDbType.TinyInt, questionnaire.VisitNumber))
                cmd.Parameters.Add(Me.CreateInputParam("@InstrumentId", SqlDbType.NVarChar, questionnaire.InstrumentID))
                cmd.Parameters.Add(Me.CreateInputParam("@Description", SqlDbType.NVarChar, questionnaire.Description))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcLastUpdate", SqlDbType.DateTime, curTime))
                cmd.Parameters.Add(Me.CreateInputParam("@LastUpdatedBy", SqlDbType.NVarChar, questionnaire.LastUpdatedBy))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Result <> 0.")
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update the instrument.")
            Throw ex
        End Try
    End Sub



    Public Overrides Function GetInstrumentIDs() As System.Data.DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_InstrumentsGetDDL", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_InstrumentsDDL")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instruments")
        End Try
        Return ds.Tables(0).DefaultView
    End Function


    Public Overrides Function GetStudyIDs() As System.Data.DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_StudyIdGetDDL", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_InstrumentsDDL")
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting instruments")
        End Try
        Return ds.Tables(0).DefaultView
    End Function




    Public Overrides Sub UpdateQuestionnaireEditingPerm( _
    ByVal id As Integer, _
    ByVal perm As String, _
    ByVal updatedBy As String)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireEditPermUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                'Throw New Exception("id = " & questionnaire.ID & ", name = " & questionnaire.Description)
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.BigInt, id))
                cmd.Parameters.Add(Me.CreateInputParam("@EditPermission", SqlDbType.NVarChar, perm))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcLastUpdate", SqlDbType.DateTime, curTime))
                cmd.Parameters.Add(Me.CreateInputParam("@LastUpdatedBy", SqlDbType.NVarChar, updatedBy))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Result <> 0.")
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update the instrument.")
            Throw ex
        End Try
    End Sub


    Public Overrides Sub UpdateQuestionnairetakingPerm( _
    ByVal id As Integer, _
    ByVal perm As String, _
    ByVal updatedBy As String)
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                conn.Open()
                Dim cmd As New SqlCommand("dotnetpm_Questionnaire_QuestionnaireTakePermUpdate", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                Dim curTime As DateTime = Me.RoundToSeconds(DateTime.UtcNow)
                'Throw New Exception("id = " & questionnaire.ID & ", name = " & questionnaire.Description)
                cmd.Parameters.Add(Me.CreateInputParam("@Id", SqlDbType.BigInt, id))
                cmd.Parameters.Add(Me.CreateInputParam("@TakePermission", SqlDbType.NVarChar, perm))
                cmd.Parameters.Add(Me.CreateInputParam("@UtcLastUpdate", SqlDbType.DateTime, curTime))
                cmd.Parameters.Add(Me.CreateInputParam("@LastUpdatedBy", SqlDbType.NVarChar, updatedBy))
                Dim p1 = New SqlParameter("@ReturnValue", SqlDbType.Int)
                p1.Direction = ParameterDirection.ReturnValue
                cmd.Parameters.Add(p1)
                cmd.ExecuteNonQuery()
                Dim result As Integer = IIf((p1.Value Is Nothing), -1, CType(p1.Value, Integer))
                If result <> 0 Then
                    Throw New ProviderException("Result <> 0.")
                End If
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            'Throw New ProviderException("Failed to update the instrument.")
            Throw ex
        End Try
    End Sub
#End Region

#Region "Reports"


    Public Overrides Function GetQuestionnaireAnswer( _
    ByVal QuestionnaireID As Integer) As System.Data.DataView
        Dim ds As New DataSet
        Try
            Dim conn As New SqlConnection(_connectionString)
            Try
                Dim cmd As SqlCommand
                cmd = New SqlCommand("dotnetpm_Questionnaire_Answer", conn)
                cmd.CommandTimeout = _CommandTimeout
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(Me.CreateInputParam("@QuestionnaireID", SqlDbType.BigInt, QuestionnaireID))
                Dim da As New SqlDataAdapter
                da.SelectCommand = cmd
                conn.Open()
                da.Fill(ds, "Questionnaire_Report")
                Dim t As DataTable = ds.Tables(0)
                Dim _iId As Integer = t.Columns.IndexOf("InstrumentID")
                Dim _uId As Integer = t.Columns.IndexOf("UID")
                Dim _qId As Integer = t.Columns.IndexOf("QuestionID")
                Dim _answer As Integer = t.Columns.IndexOf("Answer")
                Dim cAns As SqlCommand
                cAns = New SqlCommand("dotnetpm_Questionnaire_AnsMCSA", conn)
                cAns.CommandTimeout = _CommandTimeout
                cAns.CommandType = CommandType.StoredProcedure
                For Each r As DataRow In t.Rows
                    If r(_answer) = "5" Then
                        cAns.Parameters.Clear()
                        cAns.Parameters.Add(Me.CreateInputParam("@QuestionnaireID", SqlDbType.BigInt, QuestionnaireID))
                        cAns.Parameters.Add(Me.CreateInputParam("@InstrumentID", SqlDbType.VarChar, r(_iId)))
                        cAns.Parameters.Add(Me.CreateInputParam("@UID", SqlDbType.UniqueIdentifier, r(_uId)))
                        cAns.Parameters.Add(Me.CreateInputParam("@QuestionID", SqlDbType.Int, r(_qId)))
                        Dim po As New SqlParameter("@Return", SqlDbType.NVarChar, 1000)
                        po.Value = "First"
                        po.Direction = ParameterDirection.Output
                        cAns.Parameters.Add(po)
                        cAns.ExecuteNonQuery()
                        r(_answer) = po.Value
                    End If
                Next
            Finally
                conn.Close()
                conn = Nothing
            End Try
        Catch ex As Exception
            Throw ex
            'Throw New ProviderException("Error getting Questionnaire Categories")
        End Try
        Return ds.Tables(0).DefaultView
    End Function

#End Region

End Class
